import re
from urllib import request
from MysqlUtils import *
import pymysql


class SpiderTest(object):
    # 数据库链接实例化
    mysql = MysqlUtils()
    conn = mysql.get_mysql_connection()
    # 创建游标
    cursor = conn.cursor()
    # def __init__(self):
    #     self.mysql= MysqlUtils()
    #     self.db=isinstance()
    #     self.cursor=isinstance()
    # db = pymysql.connect("localhost", "root", "123456", "python_test", charset='utf8')
    # print(db)
    # 获取操作游标
    # cursor = db.cursor()
    url = 'https://coding.imooc.com/'
    div_pattern = '<div class=\'shizhan-skill clearfix\'>([\s\S]*?)</div>'
    name_pattern = 'class="">([\s\S]*?)</a>\\n'

    # 抓取页面
    def __fetch_content(self):
        opUrl = request.urlopen(SpiderTest.url)
        htmls = opUrl.read()
        htmls = str(htmls, encoding='utf-8')
        # print(htmls)
        return htmls

    # 正则分析
    def __analysis(self, htmls):
        root_html = re.findall(SpiderTest.div_pattern, htmls)
        anchors = []
        reslutNames = []
        id = 1
        for html in root_html:
            name = re.findall(SpiderTest.name_pattern, html)
            anchor = {'name': name}
            anchors.append(anchor)
        ckName = anchors[0]['name']
        for reslutName in ckName:
            reslutName = {"id": str(id), "name": reslutName}
            reslutNames.append(reslutName)
            id += 1
        return reslutNames

    def go(self):
        root_html = self.__fetch_content()
        nameList = self.__analysis(root_html)
        1 == 1
        print(nameList, end='')

        for i in range(len(nameList)):
          #  onename = nameList[i]['name']
            course_name = nameList[i]['name']
            print('名称：', course_name)

            try:
                self.cursor.execute('insert into spidertest(name) values(%s)', course_name)
                # 提交
                self.conn.commit()
            except:
                self.conn.rollback()
                print('插入数据失败：')

            # 关闭
        self.conn.close()

    # corusor=conn["cursor"]
    # print(corusor)

    # def __get_connection(self):
    #     cursor=self.cursor
    #     db=self.db
    #     print(cursor,db)
    #     result = MysqlUtils.__get_connection()

    # db = spiderTest.db
    # cursor = SpiderTest.cursor
    # for i in range(len(nameList)):
    #     onename = [nameList[i]['id'], nameList[i]['name']]
    #     name = nameList[i]['name']
    #     print(onename)
    #     try:
    #         cursor.execute('insert into spidertest(name) values(%s)', name)
    #         # 提交
    #         db.commit()
    #     except:
    #         db.rollback()
    #         print('插入数据失败：')
    #
    # # 关闭
    # db.close()
    # print(html)


spiderTest = SpiderTest()

spiderTest.go()
